
/*
 	EXEC SP_PROGRAM_RELEASE_YIELD_REPORT 3,1080,0,'09/01/2005','09/30/2005','0'
 	EXEC SP_PROGRAM_RELEASE_YIELD_REPORT 0,0,0,'0','0','0'
 */
 
 ALTER     PROC sp_program_release_yield_report

 @BASE_PRODUCT_ID	INT,	 
 @STEP_ID		INT,			--Class Store Process Step Id.s
 @VENDOR_ID		INT,
 @DATE_FROM		VARCHAR(20),
 @DATE_TO		VARCHAR(20),
 @DEFAULT_PROMPT	VARCHAR(20)
 
 AS
 
 DECLARE @RESULTS TABLE ( BASE_PRODUCT		VARCHAR(50),
			  PART_NUMBER		VARCHAR(50),
			  PROG_LOT_ID 		INT,
 			  PROG_LOT_NO		VARCHAR(50),
 			  PROG_QTY_PULLED	INT,
 			  PROG_QTY_SHIPPED	INT,
 			  PROG_YIELD		FLOAT,
 			  SHIPPED_DATE		DATETIME,
 			  ASSM_LOT_NO		VARCHAR(50),
 			  BIN_INFO		VARCHAR(50),
 			  FAB_LOT_NUMBER	VARCHAR(50),
 			  ASSEMBLY_RELEASE_DATE	DATETIME,
			  SORT_RELEASE_NUMBER	VARCHAR(50),
			  SORT_RELEASE_DATE	DATETIME,
			  TEST_PROGRAM		VARCHAR(50),
			  TEST_PROGRAM_REVISION VARCHAR(50),
			  ASSEMBLY_TEST_PROG_REV VARCHAR(50) )
 BEGIN
 
	DECLARE @BASE_PRODUCT		VARCHAR(50)
	DECLARE @PART_NUMBER		VARCHAR(50)

	DECLARE @ASSM_LOT_NO		VARCHAR(50)
	DECLARE @ASSEMBLY_RELEASE_DATE	DATETIME
	DECLARE @ASSM_PROCESSSTEP_ID	INT
	DECLARE @POST_ASSEMBLY_TEST_REV	VARCHAR(50)
	DECLARE @TEST_SPEC_FILE_ID	INT

 	DECLARE @PROG_LOT_ID 		INT
	DECLARE @PROG_QTY_PULLED	INT
 	DECLARE @PROG_QTY_SHIPPED	INT
	DECLARE @PROG_YIELD		FLOAT
	DECLARE @PROG_LOT_NO		VARCHAR(50)
	DECLARE @PROG_SHIPPED_DATE	VARCHAR(50)
 	
	DECLARE @SORT_LOT_NUMBER	VARCHAR(50)
	DECLARE @SORT_LOT_ID		INT
	--DECLARE @SORT_WAFER_IDS		VARCHAR(100)
	DECLARE @BIN_INFO		VARCHAR(50)
	
	DECLARE @SORT_RELEASE_NUMBER	VARCHAR(50)
	DECLARE @SORT_RELEASE_DATE	DATETIME
	DECLARE @TEST_PROGRAM		VARCHAR(50)
	DECLARE @SPECFILE_ID		INT
	DECLARE @BLANK_PROCESSSTEP_ID	INT		--Class Store Process Step Id.
	DECLARE @TEST_PROGRAM_REVISION	VARCHAR(50)
 
 	DECLARE @REL_DATE_FROM 		DATETIME
 	DECLARE	@REL_DATE_TO 		DATETIME
 	DECLARE @MAX_REL_DATE 		DATETIME
 	DECLARE	@MIN_REL_DATE 		DATETIME
	
	DECLARE @LOT_WAFER_ID   VARCHAR(100)

	DECLARE @WAFER_CURSOR CURSOR 	
 	DECLARE @LOT_CURSOR   CURSOR

 	DECLARE @PROCESS_STEP_ID_TABLE TABLE ( PROCESS_STEP_ID INT )
 	DECLARE @VENDOR_TABLE TABLE ( VENDOR_LOC_ID INT )
	DECLARE @PRODUCT_TABLE TABLE ( PRODUCT_ID INT )
	DECLARE @SORT_WAFERS_IDS_TABLE TABLE ( LOT_NUMBER VARCHAR(50), WAFERID VARCHAR(50) )
	DECLARE @ASSM_WAFERS_IDS_TABLE TABLE ( WAFERID INT )
 
 
 	/*
 		If No Base Product selected from report search screen, Displaying for all the Base Products.
 	*/
	
	IF ( @BASE_PRODUCT_ID = 0 )
	BEGIN
		INSERT INTO @PRODUCT_TABLE SELECT PRODUCT_ID FROM PRODUCT WHERE P_PARENT_PRODUCT_ID IS NOT NULL
	END
	ELSE
	BEGIN
		INSERT INTO @PRODUCT_TABLE SELECT PRODUCT_ID FROM PRODUCT WHERE P_PARENT_PRODUCT_ID = @BASE_PRODUCT_ID
	END
 
	/*
 		If No Part Number selected from report search screen, Displaying for all the Part Number( Class Store Part Numbers ).
 	*/
 	
	IF ( @STEP_ID = 0 )
 	BEGIN
 		INSERT INTO @PROCESS_STEP_ID_TABLE SELECT P.PROCESSSTEP_ID from product p, process_step ps where p.processstep_id = ps.processstep_id and ps.ps_type_name = 'Class Store'
 	END
 	ELSE
 	BEGIN
 		INSERT INTO @PROCESS_STEP_ID_TABLE ( PROCESS_STEP_ID ) VALUES (@STEP_ID)
 	END
 
  	/*
 		If No Vendor selected from report search screen, loading all the Vendors into the @VENDOR_TABLE table.
 	*/
  	IF ( @VENDOR_ID = 0 )
 	BEGIN
 		INSERT INTO @VENDOR_TABLE SELECT VENDOR_ID FROM VENDOR
 	END
 	ELSE
 	BEGIN
 		INSERT INTO @VENDOR_TABLE ( VENDOR_LOC_ID ) VALUES ( @VENDOR_ID )
 	END

	--For Getting the Max & Min Shipped Date of the Program Release in the System.
  	SELECT @MAX_REL_DATE = MAX( CONVERT( DATETIME, isnull ( Expected_Completion_Date, SHIPPED_DATE ) )) FROM PROGRAM_RELEASE
 	SELECT @MIN_REL_DATE = MIN( CONVERT( DATETIME, isnull ( Expected_Completion_Date, SHIPPED_DATE ) )) FROM PROGRAM_RELEASE
 
	/*
		For Assining the ( Max & Min ) dates for 'From Date' and 'To Date', if the user not selected dates for them in search screen.
	*/
 	IF ( @DATE_FROM = '0') AND (@DATE_TO = '0') 
 	BEGIN
 		SET @REL_DATE_TO = @MAX_REL_DATE
 		SET @REL_DATE_FROM = @MIN_REL_DATE
 	END
 	ELSE IF (@DATE_FROM <> '0') AND (@DATE_TO <> '0') 
 	BEGIN
 		SET @REL_DATE_TO = @DATE_TO
 		SET @REL_DATE_FROM = @DATE_FROM
 	END	
 	ELSE IF (@DATE_FROM = '0') AND (@DATE_TO <> '0') 
 	BEGIN
 		SET @REL_DATE_TO = @DATE_TO
 		SET @REL_DATE_FROM = @MIN_REL_DATE
 	END 
 	ELSE IF (@DATE_FROM <> '0') AND (@DATE_TO = '0') 
 	BEGIN
 		SET @REL_DATE_TO = @MAX_REL_DATE
 		SET @REL_DATE_FROM = @DATE_FROM	
 	END
 	ELSE
 	BEGIN
 		SET @REL_DATE_TO = @MAX_REL_DATE
 		SET @REL_DATE_FROM = @MIN_REL_DATE
 	END
 
	/*
		Getting Lots ( Class Store Lots Info ( Assembly Release Numbers) ) released for the Program Release ( Releases that are Shippped ) along
		with Pulled Qty, Shipped Qty, Class Store Part Number and Base Product Name...	
	*/

	SET @LOT_CURSOR = CURSOR FOR
 		SELECT PRLT.LOT_ID, SUM( PRLT.QTY_PULLED ) QTY_PULLED, SUM( PRLT.QTY_SHIPPED) QTY_SHIPPED, PR.BLANK_PROCESSSTEP_ID, 
			LT.LOT_NUMBER, P.PART_NUMBER, ( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = P.P_PARENT_PRODUCT_ID  ) AS BASE_PRODUCT, MAX( CONVERT( DATETIME, isnull ( PR.Expected_Completion_Date, PR.SHIPPED_DATE ) ) )
		FROM PROGRAM_RELEASE PR, PROG_RELEASE_LOT_TABLE PRLT, LOT_TABLE LT, PRODUCT P
		WHERE PR.PROG_RELEASE_ID = PRLT.PROG_RELEASE_ID 
		AND PRLT.LOT_ID = LT.LOT_ID
		AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND PR.BLANK_PROCESSSTEP_ID IN ( SELECT PROCESS_STEP_ID FROM @PROCESS_STEP_ID_TABLE PT )
		AND LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE VENDOR_ID IN ( SELECT VENDOR_LOC_ID FROM @VENDOR_TABLE ) AND L_TYPE = 'CLASS STORE')
		AND P.PRODUCT_ID IN ( SELECT PRODUCT_ID FROM @PRODUCT_TABLE)
		and isnull ( PR.Expected_Completion_Date, PR.SHIPPED_DATE ) <= CONVERT( DATETIME,@REL_DATE_TO ) AND isnull ( PR.Expected_Completion_Date, PR.SHIPPED_DATE ) >= CONVERT( DATETIME, @REL_DATE_FROM)
		AND PR.STATUS = 'SHIPPED'
		GROUP BY PRLT.LOT_ID, LT.LOT_NUMBER, PR.BLANK_PROCESSSTEP_ID, P.PART_NUMBER, P.PRODUCT_ID, P.P_PARENT_PRODUCT_ID
 	

 	OPEN @LOT_CURSOR
 		FETCH NEXT FROM @LOT_CURSOR INTO @PROG_LOT_ID, @PROG_QTY_PULLED, @PROG_QTY_SHIPPED, @BLANK_PROCESSSTEP_ID, @PROG_LOT_NO, @PART_NUMBER, @BASE_PRODUCT, @PROG_SHIPPED_DATE 
	WHILE @@FETCH_STATUS = 0
 	BEGIN
		/*
			Getting the Assembly Release related Info....by Lot at Class Store...
			select * from Assm_release_table
		*/

		SELECT @ASSM_LOT_NO = ART.ASSEMBLY_RELEASE_NO, @ASSEMBLY_RELEASE_DATE = ART.ASSEMBLY_RELEASE_DATE, 
		       @ASSM_PROCESSSTEP_ID = ART.PROCESSSTEP_ID, @SORT_LOT_NUMBER = ART.LOT_NUMBER, 
		       @SORT_LOT_ID = ARLT.LOT_ID, @BIN_INFO = ART.BIN_INFO,
		       @TEST_SPEC_FILE_ID = TEST_SPEC_FILE_ID
		FROM ASSM_RELEASE_TABLE ART, ASSM_RELEASE_LOT_TABLE ARLT
			WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID 
			AND ART.ASSEMBLY_RELEASE_NO = SUBSTRING( @PROG_LOT_NO,1 ,7 )

			SELECT @POST_ASSEMBLY_TEST_REV = POST_ASSEMBLY_TEST_REV FROM SPEC_TABLE WHERE SPECFILE_ID = @TEST_SPEC_FILE_ID
			
			IF ( @ASSM_LOT_NO IS NOT NULL ) 
			BEGIN
				/*
					Getting Assembly Wafer_Ids for the corresponding Assembly lot(from the above)
				*/
				INSERT INTO @ASSM_WAFERS_IDS_TABLE SELECT WAFER_ID FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART 
						WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID 
						AND ASSEMBLY_RELEASE_NO = @ASSM_LOT_NO AND LOT_NUMBER = @SORT_LOT_NUMBER 
						AND ART.STATUS <> 'CANCELLED'
				/*
					Getting Sort Wafer_Ids for the corresponding Sort lot and sort related info based on 
					sort lot number and wafer ids.
				*/				
				SELECT @LOT_WAFER_ID = WAFER_ID FROM  SORT_RELEASE_TABLE SRT, SORT_RELEASE_LOT_TABLE SRLT
					WHERE SRT.SORT_REL_ID = SRLT.SORT_REL_ID AND LOT_NUMBER = @SORT_LOT_NUMBER 
					
				SET @LOT_WAFER_ID = REPLACE ( @LOT_WAFER_ID ,';',',' )
				
				INSERT INTO @SORT_WAFERS_IDS_TABLE ( WAFERID ) SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @LOT_WAFER_ID )
				UPDATE @SORT_WAFERS_IDS_TABLE SET LOT_NUMBER = @SORT_LOT_NUMBER
									
				--SELECT * FROM @SORT_WAFERS_IDS_TABLE
				SELECT @SORT_RELEASE_NUMBER = SORT_REL_NUMB, @SORT_RELEASE_DATE = SORT_REL_DATE,
					@SPECFILE_ID = SPEC_FILE_ID
					FROM SORT_RELEASE_TABLE SRT,SORT_RELEASE_LOT_TABLE SRLT, @SORT_WAFERS_IDS_TABLE SWIT
					WHERE SRT.SORT_REL_ID = SRLT.SORT_REL_ID
					AND SRLT.LOT_NUMBER = SWIT.LOT_NUMBER
					AND SRLT.LOT_NUMBER = @SORT_LOT_NUMBER
					AND SWIT.WAFERID IN ( SELECT WAFERID FROM @ASSM_WAFERS_IDS_TABLE ) 
					
				SELECT @TEST_PROGRAM = Test_Program_Name,@TEST_PROGRAM_REVISION = TEST_PROGRAM_REVISION FROM SPEC_TABLE WHERE SpecFile_Id = @SPECFILE_ID

			/*IF @PROG_QTY_PULLED > 0 
			BEGIN
				SET @PROG_YIELD = ( (CONVERT( NUMERIC(9,2),@PROG_QTY_SHIPPED) / CONVERT( NUMERIC(9,2),@PROG_QTY_PULLED )) * 100 )		
			END*/
	
			INSERT INTO @RESULTS 
				( BASE_PRODUCT,  PART_NUMBER,  PROG_LOT_ID,  PROG_LOT_NO,  PROG_QTY_PULLED,  PROG_QTY_SHIPPED,  PROG_YIELD,  SHIPPED_DATE,       ASSM_LOT_NO,  BIN_INFO,  FAB_LOT_NUMBER,  ASSEMBLY_RELEASE_DATE, SORT_RELEASE_NUMBER, SORT_RELEASE_DATE, TEST_PROGRAM, TEST_PROGRAM_REVISION, ASSEMBLY_TEST_PROG_REV)
			VALUES 
				( @BASE_PRODUCT, @PART_NUMBER, @PROG_LOT_ID, @PROG_LOT_NO, @PROG_QTY_PULLED, @PROG_QTY_SHIPPED, @PROG_YIELD, @PROG_SHIPPED_DATE, @ASSM_LOT_NO, @BIN_INFO, @SORT_LOT_NUMBER,@ASSEMBLY_RELEASE_DATE,@SORT_RELEASE_NUMBER,@SORT_RELEASE_DATE,@TEST_PROGRAM,@TEST_PROGRAM_REVISION,@POST_ASSEMBLY_TEST_REV)
		END --Assembly Release Number is Not NULL.

		SET @PROG_LOT_ID 		= NULL
		SET @PROG_LOT_NO 		= NULL
		SET @PROG_QTY_PULLED 		= NULL
		SET @PROG_QTY_SHIPPED		= NULL
		SET @PROG_YIELD			= NULL
		SET @PROG_SHIPPED_DATE		= NULL
		SET @ASSM_LOT_NO		= NULL
		SET @BIN_INFO			= NULL
		SET @SORT_LOT_NUMBER		= NULL
		SET @ASSEMBLY_RELEASE_DATE	= NULL
		SET @SORT_RELEASE_NUMBER	= NULL
		SET @SORT_RELEASE_DATE		= NULL
		SET @TEST_PROGRAM		= NULL
		SET @BLANK_PROCESSSTEP_ID	= NULL
		SET @BASE_PRODUCT		= NULL
		SET @PART_NUMBER		= NULL
		SET @SPECFILE_ID		= NULL
		SET @LOT_WAFER_ID 		= NULL
		SET @POST_ASSEMBLY_TEST_REV	= NULL
		SET @TEST_SPEC_FILE_ID		= NULL
		SET @TEST_PROGRAM_REVISION	= NULL
		SET @ASSM_PROCESSSTEP_ID	= NULL
		SET @SORT_LOT_ID		= NULL

		DELETE FROM @SORT_WAFERS_IDS_TABLE
		DELETE FROM @ASSM_WAFERS_IDS_TABLE
		
		FETCH NEXT FROM @LOT_CURSOR INTO @PROG_LOT_ID, @PROG_QTY_PULLED, @PROG_QTY_SHIPPED, @BLANK_PROCESSSTEP_ID, @PROG_LOT_NO, @PART_NUMBER, @BASE_PRODUCT, @PROG_SHIPPED_DATE 
 	END	--Lot Cursor End.
 	
 	CLOSE @LOT_CURSOR
 	DEALLOCATE @LOT_CURSOR
 	SELECT BASE_PRODUCT, PART_NUMBER, SUM(PROG_QTY_PULLED) PROG_QTY_PULLED, SUM(PROG_QTY_SHIPPED) PROG_QTY_SHIPPED, ( CONVERT( NUMERIC(9,2) , SUM(PROG_QTY_SHIPPED)) * 100 / CONVERT( NUMERIC(9,2) , SUM(PROG_QTY_PULLED)) )  PROG_YIELD, MAX(SHIPPED_DATE) SHIPPED_DATE, ASSM_LOT_NO, BIN_INFO, FAB_LOT_NUMBER, ASSEMBLY_RELEASE_DATE, SORT_RELEASE_NUMBER, SORT_RELEASE_DATE, TEST_PROGRAM, TEST_PROGRAM_REVISION, ASSEMBLY_TEST_PROG_REV
	FROM @RESULTS
	GROUP BY BASE_PRODUCT, PART_NUMBER, SUBSTRING(PROG_LOT_NO,1,7),  ASSM_LOT_NO, BIN_INFO, FAB_LOT_NUMBER, ASSEMBLY_RELEASE_DATE, SORT_RELEASE_NUMBER, SORT_RELEASE_DATE, TEST_PROGRAM, TEST_PROGRAM_REVISION, ASSEMBLY_TEST_PROG_REV
	ORDER BY BASE_PRODUCT ,PART_NUMBER, ASSM_LOT_NO DESC 
 	--SELECT * FROM @RESULTS ORDER BY BASE_PRODUCT ,PART_NUMBER, ASSM_LOT_NO DESC 
 END

